/*
	EXEC sp_assembly_instruction_report '0','0','0','0','0','0'

	EXEC sp_assembly_instruction_report '0','2092','0','0','0','0'
*/
ALTER PROC sp_assembly_instruction_report

@productId		VARCHAR(50),
@step_ids		VARCHAR(200),
@dateType		VARCHAR(50),
@fromDate		VARCHAR(50),
@toDate			VARCHAR(50),
@defaultPrompt		VARCHAR(50)
AS
BEGIN
	DECLARE @RESULTS TABLE ( 
			[Mkt Part Number] VARCHAR(50),[Package Type] Varchar(50),[Buffer] int, [Backlog] int, 
			[Total Order] int, [Total Wip] int, [Bal to Release] int,[Issued Assm Req Qty] int,
			[AL Number] varchar(255), [GDW] int, [B/E Part Number] varchar(50),FGPROCESSSTEP_ID INT )	

	DECLARE @PROCESSSTEPS_IDS_FOR_FG_FLOW TABLE ( PROCESSSTEP_ID INT, PS_TYPE VARCHAR(50), PS_TYPE_NAME VARCHAR(50), PART_NUMBER VARCHAR(50) )
	
	DECLARE @PACKAGE_TYPE		VARCHAR(50)
	DECLARE @MKT_PART_NUMBER 	VARCHAR(50)
	DECLARE @RELEASE_NO		VARCHAR(50)
	DECLARE @DBPART_NUMBER		VARCHAR(50)
	DECLARE @LOT_NUMBER		VARCHAR(50)
	DECLARE @REL_STATUS 		VARCHAR(50)

	DECLARE @GROSSDIE		INT
	DECLARE @FGPROCESSSTEP_ID	INT
	DECLARE @REQUESTED_QTY		INT
	DECLARE @BUFFER_QTY		INT
	DECLARE @RELEASE_ID		INT
	DECLARE @ASSM_STEP_ID 		INT
	DECLARE @ARRPOVED_QTY		INT
	DECLARE @BACKLOG		INT
	DECLARE @SHIPQTY		INT
	DECLARE @LOT_ID			INT
	DECLARE @TOTAL_QTY		INT
	DECLARE @ASSMQTY_IN		INT
	DECLARE @TESTQTY_IN		INT
	DECLARE @TRQTY_IN		INT
	DECLARE @FGQTY_IN		INT

	DECLARE @RELEASE_NO_STRING	VARCHAR(255)

	DECLARE @DATE 		VARCHAR(50)
	DECLARE @STARTMONTH	INT
	DECLARE @STARTYEAR	INT
	DECLARE @STARTDATE 	DATETIME
	DECLARE @ENDDATE	DATETIME
	
	DECLARE @PROCESSCURSOR  CURSOR
	DECLARE @RELEASECURSOR  CURSOR
	DECLARE @LOTCURSOR	CURSOR

	IF @productId IS NOT NULL AND @productId != '0'
	BEGIN
		IF @step_ids IS NOT NULL AND @step_ids != '0'
		BEGIN
			SET @PROCESSCURSOR = CURSOR FOR SELECT DISTINCT FG_PROCESSSTEP_ID FROM ASSM_RELEASE_TABLE WHERE FG_PROCESSSTEP_ID IN  ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @step_ids ) )
		END
		ELSE
		BEGIN
			SET @PROCESSCURSOR = CURSOR FOR SELECT DISTINCT FG_PROCESSSTEP_ID FROM ASSM_RELEASE_TABLE WHERE FG_PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PRODUCT WHERE P_PARENT_PRODUCT_ID = @productId )
		END
	END
	ELSE
	BEGIN
		IF @step_ids IS NOT NULL AND @step_ids != '0'
		BEGIN
			SET @PROCESSCURSOR = CURSOR FOR SELECT DISTINCT FG_PROCESSSTEP_ID FROM ASSM_RELEASE_TABLE WHERE FG_PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @step_ids ) )
		END
		ELSE
		BEGIN
			SET @PROCESSCURSOR = CURSOR FOR SELECT DISTINCT FG_PROCESSSTEP_ID FROM ASSM_RELEASE_TABLE 
		END
	END

	if @fromDate is not null and @fromDate != '0'
	begin
		set @STARTDATE = convert(datetime,@fromDate)
	end
	else
	begin
		set @STARTDATE = current_timestamp
	end

	SET @STARTMONTH = DATEPART ( MM, @STARTDATE )
	SET @STARTYEAR = DATEPART ( YYYY, @STARTDATE )
	

	if @toDate != '0' AND @fromDate != '0'
	begin
		SET @STARTDATE = CONVERT(DATETIME, CONVERT(VARCHAR,@fromDate,101)+' 12:00AM' )
		SET @ENDDATE = CONVERT(DATETIME, CONVERT(VARCHAR,@toDate,101)+' 11:59PM' )
	end
	ELSE 
	BEGIN
		SET @date = CONVERT(VARCHAR,@STARTMONTH)+'/'+'01/'+CONVERT(VARCHAR,@STARTYEAR)
		SET @STARTDATE = dateadd(m,datediff(m,0, @date),0)
		SET @ENDDATE = dateadd(ms, -3, dateadd (m,datediff(m,0,dateadd(m,12,@date)),0))
	END
	PRINT '@STARTDATE ='+CONVERT(VARCHAR,@STARTDATE)
	PRINT '@ENDDATE ='+CONVERT(VARCHAR,@ENDDATE)

	--DECLARE 

	OPEN @PROCESSCURSOR
	FETCH NEXT FROM @PROCESSCURSOR INTO @FGPROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--INSERTING BACKWARD FLOW PROCESSSTEPS 
		--PRINT '@FGPROCESSSTEP_ID ='+CONVERT(VARCHAR,@FGPROCESSSTEP_ID)
		INSERT INTO @PROCESSSTEPS_IDS_FOR_FG_FLOW (PROCESSSTEP_ID, PS_TYPE,PS_TYPE_NAME, PART_NUMBER ) SELECT DISTINCT PROCESSSTEP_ID,PS_TYPE,PS_TYPE_NAME, PRODUCT_NAME FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @FGPROCESSSTEP_ID,0 ) 
		
		--GETTING BACKLOG.
/*
		SELECT @backlog  = SUM( COOL.COOL_QTY), @SHIPQTY =  SELECT SUM( ISNULL( CSLIT_QTY_SHIPPED, 0 ) ) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = COOL.LINEITEM_ID  AND CSLIT_STATUS = 'SHIPPED') )
		FROM 
			Product,Customer_Open_Order_LineItem COOL
		WHERE COOL.Product_ID = Product.Product_ID 
		AND ( isnull(isnull(COOL.COOL_REVISED_DATE,COOL.COOL_SCHEDULE_DATE),COOL.COOL_Requested_Date )>=@STARTDATE and isnull( isnull(COOL.COOL_REVISED_DATE,COOL.COOL_SCHEDULE_DATE),COOL.COOL_Requested_Date )<=@ENDDATE)
		and product.processstep_id = @fgprocessstep_id
		and COOL_Status = 'APPROVED'
		*/
		PRINT @FGPROCESSSTEP_ID
		SELECT 
			@backlog = ISNULL(SUM(COOL.COOL_qty), 0)
		from 
			product,Customer_Open_Order_LineItem COOL
		where 
			COOL.Product_ID=product.Product_ID 
			and ( isnull(isnull(COOL.COOL_REVISED_DATE,COOL.COOL_SCHEDULE_DATE),COOL.COOL_Requested_Date )>=@STARTDATE and isnull( isnull(COOL.COOL_REVISED_DATE,COOL.COOL_SCHEDULE_DATE),COOL.COOL_Requested_Date )<=@ENDDATE)
			and product.processstep_id = @fgprocessstep_id
			and COOL_Status <> 'hold'
		GROUP BY 
			COOL.Product_ID

		SELECT 
			@SHIPQTY = ISNULL(SUM(CSLIT.CSLIT_QTY_SHIPPED), 0)
		from 
			product,Customer_Open_Order_LineItem COOL, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT
		where 
			COOL.LineItem_Id = CSLIT.LineItem_ID 
			and COOL.Product_ID=product.Product_ID 
			and ( isnull(isnull(COOL.COOL_REVISED_DATE,COOL.COOL_SCHEDULE_DATE),COOL.COOL_Requested_Date )>=@STARTDATE and isnull( isnull(COOL.COOL_REVISED_DATE,COOL.COOL_SCHEDULE_DATE),COOL.COOL_Requested_Date )<=@ENDDATE)
			and product.processstep_id = @fgprocessstep_id
			and COOL_Status <> 'hold'
		GROUP BY 
			COOL.Product_ID
		/*SELECT 
			@backlog = ( ISNULL(SUM(COOL.COOL_qty), 0) - ( SELECT ISNULL(SUM(CSLIT.CSLIT_QTY_SHIPPED),0) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COL 
							WHERE CSLIT.LINEITEM_ID = COL.LINEITEM_ID  AND CSLIT_STATUS = 'SHIPPED' and COL.COOL_Status <> 'hold' and (COL.COOL_Requested_Date>=@STARTDATE and COL.COOL_Requested_Date<=@ENDDATE) AND COL.Product_Id = Product.Product_Id ) )
		from 
			product,Customer_Open_Order_LineItem COOL
		where 
			COOL.Product_ID=product.Product_ID 
			and ( isnull(isnull(COOL.COOL_REVISED_DATE,COOL.COOL_SCHEDULE_DATE),COOL.COOL_Requested_Date )>=@STARTDATE and isnull( isnull(COOL.COOL_REVISED_DATE,COOL.COOL_SCHEDULE_DATE),COOL.COOL_Requested_Date )<=@ENDDATE)
			and product.processstep_id = @fgprocessstep_id
			and COOL_Status <> 'hold'
		GROUP BY 
			COOL.Product_ID
		*/
		SET @backlog = ISNULL(@backlog, 0) - ISNULL(@SHIPQTY, 0)
		SELECT @MKT_PART_NUMBER = P_MARKETING_PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @FGPROCESSSTEP_ID

		--TOTAL APPROVED QTY BUT NOT CLOSE IN RELEASE FOR CORRESPONDING FG
		SELECT @ARRPOVED_QTY = SUM(QTY_PULLED)
		from 
			assm_release_table ART, ASSM_RELEASE_LOT_TABLE ARLT 
		where 
			ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID 
			AND FG_PROCESSSTEP_ID = @FGPROCESSSTEP_ID 
			AND ART.STATUS = 'APPROVED'
			--AND ( ASSEMBLY_RELEASE_DATE >=@STARTDATE AND ASSEMBLY_RELEASE_DATE<=@ENDDATE )
		GROUP BY 
			FG_PROCESSSTEP_ID,PACKAGE_TYPE

		--SELECT TOP 1 @PACKAGE_TYPE = PACKAGE_TYPE FROM assm_release_table WHERE FG_PROCESSSTEP_ID = @FGPROCESSSTEP_ID AND PACKAGE_TYPE IS NOT NULL
		select @BUFFER_QTY = convert(int,cast(parameter_value as float)) from process_parameter where parameter_key = 'Reorder Level' and processstep_id = @FGPROCESSSTEP_ID
		select @GROSSDIE = convert(int,cast(parameter_value as float)) from process_parameter where parameter_key = 'Gross Die/Wafer' and processstep_id = ( SELECT PROCESSSTEP_ID FROM @PROCESSSTEPS_IDS_FOR_FG_FLOW WHERE PS_TYPE = 'FAB')
		select @PACKAGE_TYPE = parameter_value from process_parameter where parameter_key = 'package id' and processstep_id = ( SELECT PROCESSSTEP_ID FROM @PROCESSSTEPS_IDS_FOR_FG_FLOW WHERE PS_TYPE = 'Assembly')
				
		SELECT @DBPART_NUMBER = PART_NUMBER FROM @PROCESSSTEPS_IDS_FOR_FG_FLOW WHERE PS_TYPE = 'DIE BANK'
		
		--TOTAL ORDER QTY IN RELEASE FOR CORRESPONDING FG
		SET @REQUESTED_QTY = ISNULL(@backlog,0) + ISNULL(@BUFFER_QTY,0)

		--CONSTRUCTING RELEASE NO STRING
		SET @RELEASECURSOR = CURSOR FOR select assembly_release_id,assembly_release_no,PROCESSSTEP_ID,status from assm_release_table where FG_PROCESSSTEP_ID = @FGPROCESSSTEP_ID and STATUS != 'CANCELLED' --AND ( ASSEMBLY_RELEASE_DATE >=@STARTDATE AND ASSEMBLY_RELEASE_DATE<=@ENDDATE )
		OPEN @RELEASECURSOR
		FETCH NEXT FROM @RELEASECURSOR INTO @RELEASE_ID,@RELEASE_NO,@ASSM_STEP_ID,@REL_STATUS
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @LOTCURSOR = CURSOR FOR SELECT DISTINCT LOT_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID
			OPEN @LOTCURSOR
			FETCH NEXT FROM @LOTCURSOR INTO @LOT_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SELECT @LOT_NUMBER = LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
				
				select @ASSMQTY_IN = SUM(Qty_in) from lot_table where fab_lot_number = @LOT_NUMBER  and lot_number = @RELEASE_NO and LOT_CURRENT_STATUS = 'RUNNING' AND processstep_id IN ( SELECT PROCESSSTEP_ID FROM @PROCESSSTEPS_IDS_FOR_FG_FLOW WHERE PS_TYPE_NAME = 'Assembly')
				
				select @TESTQTY_IN = SUM(Qty_in) from lot_table where fab_lot_number = @LOT_NUMBER and lot_number = @RELEASE_NO and LOT_CURRENT_STATUS = 'RUNNING' AND  processstep_id IN ( SELECT DISTINCT PROCESSSTEP_ID FROM @PROCESSSTEPS_IDS_FOR_FG_FLOW WHERE PS_TYPE_NAME = 'TEST')
					
				select @TRQTY_IN = SUM(Qty_in) from lot_table where fab_lot_number = @LOT_NUMBER and lot_number = @RELEASE_NO and LOT_CURRENT_STATUS = 'RUNNING' AND  processstep_id IN ( SELECT PROCESSSTEP_ID FROM @PROCESSSTEPS_IDS_FOR_FG_FLOW WHERE PS_TYPE_NAME = 'CLASS STORE')
					
				select @FGQTY_IN = SUM(Qty_in) from lot_table where fab_lot_number = @LOT_NUMBER and lot_number = @RELEASE_NO and LOT_CURRENT_STATUS = 'RUNNING' AND  processstep_id = @FGPROCESSSTEP_ID
					
				IF @TOTAL_QTY > 0
				BEGIN
					SET @TOTAL_QTY = @TOTAL_QTY + ( ISNULL(@ASSMQTY_IN,0)+ISNULL(@TESTQTY_IN,0)+ISNULL(@TRQTY_IN,0)+ISNULL(@FGQTY_IN,0) )
				END
				ELSE
				BEGIN
					SET @TOTAL_QTY =  ISNULL(@ASSMQTY_IN,0)+ISNULL(@TESTQTY_IN,0)+ISNULL(@TRQTY_IN,0)+ISNULL(@FGQTY_IN,0)
				END
				
					SET @ASSMQTY_IN = NULL
					SET @TESTQTY_IN = NULL
					SET @TRQTY_IN 	= NULL
					SET @FGQTY_IN 	= NULL

			FETCH NEXT FROM @LOTCURSOR INTO @LOT_ID
			END
	 		CLOSE @LOTCURSOR
	 		DEALLOCATE @LOTCURSOR
			
			IF @REL_STATUS != 'CLOSED'
			BEGIN
				IF ( @RELEASE_NO_STRING IS NULL )
				BEGIN
					SET @RELEASE_NO_STRING = @RELEASE_NO  
				END
				ELSE
				BEGIN
					SET @RELEASE_NO_STRING = @RELEASE_NO_STRING +','+ @RELEASE_NO  
				END
			END
				
		FETCH NEXT FROM @RELEASECURSOR INTO @RELEASE_ID,@RELEASE_NO,@ASSM_STEP_ID,@REL_STATUS
		END
	 	CLOSE @RELEASECURSOR
	 	DEALLOCATE @RELEASECURSOR
	
		--INSERTING TOTAL RESULTS
		INSERT INTO  @RESULTS
			(FGPROCESSSTEP_ID,[Mkt Part Number],[Package Type],[Buffer],[Total Order],[AL Number],[B/E Part Number],[GDW],[Issued Assm Req Qty],[Backlog],[Total Wip],[Bal to Release])
		VALUES
			(@FGPROCESSSTEP_ID,@MKT_PART_NUMBER,@PACKAGE_TYPE,@BUFFER_QTY,@REQUESTED_QTY,@RELEASE_NO_STRING,@DBPART_NUMBER,@GROSSDIE,@ARRPOVED_QTY,@backlog,@TOTAL_QTY,(@REQUESTED_QTY-@TOTAL_QTY))

		SET @backlog	= NULL
		SET @SHIPQTY	= NULL
		SET @ARRPOVED_QTY = NULL
		SET @REQUESTED_QTY = NULL
		SET @RELEASE_NO_STRING 	= NULL
		SET @TOTAL_QTY		= NULL

		DELETE FROM @PROCESSSTEPS_IDS_FOR_FG_FLOW

	FETCH NEXT FROM @PROCESSCURSOR INTO @FGPROCESSSTEP_ID
	END
 	CLOSE @PROCESSCURSOR
 	DEALLOCATE @PROCESSCURSOR	

SELECT 
	[Mkt Part Number],[Package Type],[Buffer],[Backlog],[Total Order], [Total Wip], 
	[Bal to Release],[Issued Assm Req Qty],[AL Number],[GDW],[B/E Part Number] --,FGPROCESSSTEP_ID
FROM 
	@RESULTS 
WHERE 
	[Mkt Part Number] IS NOT NULL
ORDER BY
	[Mkt Part Number]

END

